"""
host: “211.103.136.244”
port: 7061
user: “student”
password: “iHRM_student_2021”
database: “ihrm”
table："bs_user"
"""
import pymysql


class DBUtil(object):
    conn = None
    @classmethod
    def __get_conn(cls):
        """
        获取数据库连接
        :return: 返回数据库连接对象
        """
        if cls.conn is None:
            cls.conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                                       password="iHRM_student_2021", database="ihrm", charset="utf8")
        return cls.conn

    @classmethod
    def __close_conn(cls):
        """
        关闭数据库连接
        :return: 无
        """
        if cls.conn:
            cls.conn.close()
            cls.conn = None

    @classmethod
    def get_one(cls, sql):
        """
        执行查询sql语句，获取一条记录
        :param sql: 查询sql
        :return: 一条记录内容
        """
        cursor = None
        res = None
        try:
            # 获取连接，获取游标
            cursor = cls.__get_conn().cursor()
            # 使用游标执行sql
            cursor.execute(sql)
            # 从结果集提取一条记录
            res = cursor.fetchone()
        except Exception as err:
            print("执行查询sql异常：", str(err))
            raise err
        finally:
            cursor.close()
            cls.__close_conn()
            # 返回记录内容
            return res

    @classmethod
    def uid_db(cls, sql):
        """
        执行数据库增删改sql操作
        :param sql: 增删改sql
        :return: sql影响的行数
        """
        cursor = None
        res = None
        try:
            cursor = cls.__get_conn().cursor()      # 获取游标
            cursor.execute(sql)         # 执行sql
            res = cursor.rowcount       # 获取影响的行数
            cls.conn.commit()           # 提交事务
        except Exception as err:
            print("修改数据库异常：", str(err))     # 打印错误信息
            cls.conn.rollback()         # 回滚事务
            raise err                   # 抛出异常
        finally:
            cursor.close()              # 关闭游标
            cls.__close_conn()          # 关闭连接
            return res                  # 返回影响的行数


if __name__ == '__main__':
    sql_query = "select * from bs_user where mobile = '13800000002'"
    res = DBUtil.get_one(sql_query)
    print(res)

    sql_del = "delete from bs_user where id = '111177778888'"
    res = DBUtil.uid_db(sql_del)
    print("影响的行数:", res)